﻿using Delphi.Isis.EodServiceReference;
using Delphi.Mimir.Data.Entities.Raw;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Delphi.Mimir.Data.Bulk {
    public class BulkLoad {

        private static BulkLoad _bulkLoad;
        private string ConnectionString { set; get; }


        private BulkLoad() {
            ConnectionString = ConfigurationManager.ConnectionStrings["Delphi"].ConnectionString;
        }

        public static BulkLoad GetInstance() {
            if (_bulkLoad == null) {
                _bulkLoad = new BulkLoad();
            }
            return _bulkLoad;
        }

        public void update(DataTable table, string tableName, int batchSize) {

            using (SqlConnection conn = new SqlConnection(ConnectionString)) {
                conn.Open();
                using (SqlBulkCopy bulk = new SqlBulkCopy(conn)) {
                    bulk.DestinationTableName = tableName;
                    bulk.BatchSize = batchSize;
                    bulk.WriteToServer(table);
                }
            }
        }


        public void Persist(EXCHANGE[] exchanges) {
            if (exchanges == null) {
                return;
            }

            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("exc_cd", typeof(string)));
            table.Columns.Add(new DataColumn("exc_desc", typeof(string)));
            table.Columns.Add(new DataColumn("exc_cnt_cd", typeof(string)));
            table.Columns.Add(new DataColumn("exc_cur_cd", typeof(string)));
            table.Columns.Add(new DataColumn("exc_suffix", typeof(string)));
            table.Columns.Add(new DataColumn("exc_time_zone_desc", typeof(string)));
            table.Columns.Add(new DataColumn("exc_intraday_cd", typeof(string)));

            foreach (EXCHANGE exc in exchanges) {
                DataRow row = table.NewRow();
                row["exc_cd"] = exc.Code;
                row["exc_desc"] = exc.Name;
                row["exc_cnt_cd"] = exc.Country;
                row["exc_cur_cd"] = exc.Currency;
                row["exc_suffix"] = exc.Suffix;
                row["exc_time_zone_desc"] = exc.TimeZone;
                row["exc_intraday_cd"] = exc.IsIntraday;
                table.Rows.Add(row);
            }
            update(table, "raw_eod_exchanges", 1000);
        }

        public void Persist(SYMBOL[] symbols, string exchangeCode, bool isOption = false) {
            if (symbols == null) {
                return;
            }

            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("sym_id", typeof(int)));
            table.Columns.Add(new DataColumn("sym_cd", typeof(string)));
            table.Columns.Add(new DataColumn("sym_desc", typeof(string)));
            table.Columns.Add(new DataColumn("sym_exc_cd", typeof(string)));
            table.Columns.Add(new DataColumn("sym_load_ts", typeof(DateTime)));

            foreach (SYMBOL sym in symbols) {
                DataRow row = table.NewRow();
                
                row["sym_cd"] = sym.Code;
                row["sym_desc"] = sym.LongName;
                row["sym_exc_cd"] = exchangeCode;
                row["sym_load_ts"] = DateTime.UtcNow;
                
                table.Rows.Add(row);
            }

            update(table, "raw.eod_symbols", 1000);

            // special treatment only for options!
            if (isOption) {
                table = new DataTable();
                table.Columns.Add(new DataColumn("opt_sym_cd", typeof(string)));
                table.Columns.Add(new DataColumn("opt_underlying_sym_cd", typeof(string)));
                table.Columns.Add(new DataColumn("opt_exc_cd", typeof(string)));
                table.Columns.Add(new DataColumn("opt_strike", typeof(decimal)));
                table.Columns.Add(new DataColumn("opt_excercise_dt", typeof(DateTime)));
                table.Columns.Add(new DataColumn("opt_type_fg", typeof(string)));

                foreach (SYMBOL sym in symbols) {
                    DataRow row = table.NewRow();

                    string[] str = sym.Name.Split(default(string[]), StringSplitOptions.RemoveEmptyEntries);
                    row["opt_sym_cd"] = sym.Code;
                    row["opt_underlying_sym_cd"] = str[0];
                    row["opt_exc_cd"] = exchangeCode;
                    row["opt_strike"] = decimal.Parse(str[2]);
                    row["opt_excercise_dt"] = DateTime.ParseExact(str[1], "dd-MMM-yyyy", System.Globalization.CultureInfo.InvariantCulture);
                    switch (str[3].ToUpper()) {
                        case "CALL":
                            {
                                row["opt_type_fg"] = 'C';
                                break;
                            }
                        case "PUT":
                            {
                                row["opt_type_fg"] = 'P';
                                break;
                            }
                        default:
                            row["opt_type_fg"] = null;
                            break;
                    };
                    table.Rows.Add(row);
                }
                update(table, "raw_eod_options", 1000);
            }
            // end Option

        }

        public void Persist(RawQuote[] quotes) {
            if (quotes == null) {
                return;
            }

            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("qot_id", typeof(Int64)));
            table.Columns.Add(new DataColumn("qot_sym_cd", typeof(string)));
            table.Columns.Add(new DataColumn("qot_exc_cd", typeof(string)));
            table.Columns.Add(new DataColumn("qot_trade_dt", typeof(DateTime)));
            table.Columns.Add(new DataColumn("qot_period", typeof(string)));
            table.Columns.Add(new DataColumn("qot_open", typeof(decimal)));
            table.Columns.Add(new DataColumn("qot_high", typeof(decimal)));
            table.Columns.Add(new DataColumn("qot_low", typeof(decimal)));
            table.Columns.Add(new DataColumn("qot_close", typeof(decimal)));
            table.Columns.Add(new DataColumn("qot_volume", typeof(Int64)));
            table.Columns.Add(new DataColumn("qot_open_inerest", typeof(int)));
            table.Columns.Add(new DataColumn("qot_adj_factor", typeof(decimal)));

            foreach (RawQuote q in quotes) {
                DataRow row = table.NewRow();
                row["qot_sym_cd"] = q.qot_sym_cd;
                row["qot_exc_cd"] = q.qot_exc_cd;
                row["qot_trade_dt"] = q.qot_trade_dt;
                row["qot_period"] = q.qot_period;
                row["qot_open"] = q.qot_open;
                row["qot_high"] = q.qot_high;
                row["qot_low"] = q.qot_low;
                row["qot_close"] = q.qot_close;
                row["qot_volume"] = q.qot_volume;
                if(q.qot_open_interest != null) {
                    row["qot_open_inerest"] = q.qot_open_interest;
                }
                if(q.qot_adj_factor != null) {
                    row["qot_adj_factor"] = q.qot_adj_factor;
                }

                table.Rows.Add(row);
            }
            update(table, "raw.eod_quotes", 1000);
        }

        public void Persist(QUOTE[] quotes, string excangeCode, string period) {
            if (quotes == null) {
                return;
            }

            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("qot_id", typeof(Int64)));
            table.Columns.Add(new DataColumn("qot_sym_cd", typeof(string)));
            table.Columns.Add(new DataColumn("qot_exc_cd", typeof(string)));
            table.Columns.Add(new DataColumn("qot_trade_dt", typeof(DateTime)));
            table.Columns.Add(new DataColumn("qot_period", typeof(string)));
            table.Columns.Add(new DataColumn("qot_open", typeof(decimal)));
            table.Columns.Add(new DataColumn("qot_high", typeof(decimal)));
            table.Columns.Add(new DataColumn("qot_low", typeof(decimal)));
            table.Columns.Add(new DataColumn("qot_close", typeof(decimal)));
            table.Columns.Add(new DataColumn("qot_volume", typeof(Int64)));
            table.Columns.Add(new DataColumn("qot_open_inerest", typeof(int)));
            
            foreach (QUOTE q in quotes) {
                DataRow row = table.NewRow();
                row["qot_sym_cd"] = q.Symbol;
                row["qot_exc_cd"] = excangeCode;
                row["qot_trade_dt"] = q.DateTime;
                row["qot_period"] = period;
                row["qot_open"] = q.Open;
                row["qot_high"] = q.High;
                row["qot_low"] = q.Low;
                row["qot_close"] = q.Close;
                row["qot_volume"] = q.Volume;
                row["qot_open_inerest"] = q.OpenInterest;

                table.Rows.Add(row);
            }
            update(table, "raw.eod_quotes", 1000);

        }

        public void Persist(SYMBOLCHANGE[] changes) {
            if(changes == null) {
                return;
            }

            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("syc_id", typeof(int)));
            table.Columns.Add(new DataColumn("syc_exc_cd_old", typeof(string)));
            table.Columns.Add(new DataColumn("syc_sym_cd_old", typeof(string)));
            table.Columns.Add(new DataColumn("syc_exc_cd_new", typeof(string)));
            table.Columns.Add(new DataColumn("syc_sym_cd_new", typeof(string)));
            table.Columns.Add(new DataColumn("syc_change_dt", typeof(DateTime)));

            foreach(SYMBOLCHANGE c in changes) {
                DataRow row = table.NewRow();
                row["syc_exc_cd_old"] = c.ExchangeCode;
                row["syc_sym_cd_old"] = c.OldSymbol;
                row["syc_exc_cd_new"] = c.NewExchangeCode;
                row["syc_sym_cd_new"] = c.NewSymbol;
                row["syc_change_dt"] = c.DateTime;
                table.Rows.Add(row);
            }
            update(table, "raw.eod_symbol_changes", 1000);
        }

        public void Persist(SPLIT[] splits) {
            if (splits == null) {
                return;
            }

            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("syp_id", typeof(int)));
            table.Columns.Add(new DataColumn("syp_exc_cd", typeof(string)));
            table.Columns.Add(new DataColumn("syp_sym_cd", typeof(string)));
            table.Columns.Add(new DataColumn("syp_split_dt", typeof(DateTime)));
            table.Columns.Add(new DataColumn("syp_ratio", typeof(string)));

            foreach (SPLIT s in splits) {
                DataRow row = table.NewRow();
                row["syp_exc_cd"] = s.Exchange;
                row["syp_sym_cd"] = s.Symbol;
                row["syp_split_dt"] = s.DateTime;
                row["syp_ratio"] = s.Ratio;
                table.Rows.Add(row);
            }
            update(table, "raw.eod_splits", 1000);
        }

        public void Persist(FUNDAMENTAL[] fundamentals, string exchangeCode) {
            if(fundamentals == null) {
                return;
            }

            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("fun_id", typeof(Int64)));
            table.Columns.Add(new DataColumn("fun_sym_cd", typeof(string)));
            table.Columns.Add(new DataColumn("fun_exc_cd", typeof(string)));
            table.Columns.Add(new DataColumn("fun_dt", typeof(DateTime)));
            table.Columns.Add(new DataColumn("fun_ind_desc", typeof(string)));
            table.Columns.Add(new DataColumn("fun_sct_desc", typeof(string)));
            table.Columns.Add(new DataColumn("fun_shares", typeof(Int64)));
            table.Columns.Add(new DataColumn("fun_market_cap", typeof(Int64)));
            table.Columns.Add(new DataColumn("fun_pe", typeof(decimal)));
            table.Columns.Add(new DataColumn("fun_eps", typeof(decimal)));
            table.Columns.Add(new DataColumn("fun_div_yield", typeof(decimal)));
            table.Columns.Add(new DataColumn("fun_ebitda", typeof(Int64)));
            table.Columns.Add(new DataColumn("fun_peg", typeof(decimal)));
            table.Columns.Add(new DataColumn("fun_pts", typeof(decimal)));
            table.Columns.Add(new DataColumn("fun_ptb", typeof(decimal)));
            table.Columns.Add(new DataColumn("fun_yield", typeof(decimal)));

            foreach(FUNDAMENTAL f in fundamentals) {
                DataRow row = table.NewRow();
                row["fun_dt"] = f.DateTime;
                row["fun_sym_cd"] = f.Symbol;
                row["fun_exc_cd"] = exchangeCode;
                row["fun_ind_desc"] = f.Industry;
                row["fun_sct_desc"] = f.Sector;
                row["fun_shares"] = f.Shares;
                row["fun_market_cap"] = f.MarketCap;
                row["fun_pe"] = f.PE;
                row["fun_eps"] = f.EPS;
                row["fun_div_yield"] = f.DivYield;
                row["fun_ebitda"] = f.EBITDA;
                row["fun_peg"] = f.PEG;
                row["fun_pts"] = f.PtS;
                row["fun_ptb"] = f.PtB;
                row["fun_yield"] = f.Yield;
                table.Rows.Add(row);
            }
            update(table, "raw.eod_fundamentals", 1000);
        }
    }
}
